package cn.com.nes.site.module.dao.monitor;

import cn.com.nes.common.util.DBUtils;
import cn.com.nes.common.util.Page;
import cn.com.nes.common.util.UuidUtils;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ParamDaoImpl extends BaseDaoImpl {

    public PageData addCustomParam(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("新增自定义参数失败");
        Connection conn = null;
        Connection conn_ope = null;
        StringBuffer sb = new StringBuffer();
        List<Object> resList = new ArrayList<Object>();
        Map<String, Object> dateRes = new LinkedHashMap<String, Object>();
        try {
            conn = DBUtils.getJNDIDBConn();
            conn_ope = DBUtils.getJdbcForOpe();

            List<String> sbids = (List<String>) req.getReqData().get("sbids");
            // 查询当前配置预警规则的设备是否为同一协议，不相同，不能进行预警规则保存
            sb.append("select count(distinct c.protocol_id) from is_equipment_info a left join is_wxsb b on b.wx_id = a.wx_id left join is_device_protocol_info c on c.protocol_id = b.protocol_id where a.sb_id in (");
            for (int i = 0; i < sbids.size(); i++) {
                sb.append("'" + sbids.get(i) + "'");
                if (i < sbids.size() - 1) {
                    sb.append(",");
                }
            }
            sb.append(")");
            int protocolCount = this.queryForInt(conn_ope, sb.toString(), new Object[]{});
            if (protocolCount > 1) {
                res.setRetCode(9);
                res.setRetMessage("当前选中设备不是同一个协议，请选择相同协议的设备");
                return res;
            }

            String userId = req.getUinfo().getId();
            String name = (String) req.getReqData().get("name");
            String formula = (String) req.getReqData().get("formula");
            String paramId = (String) req.getReqData().get("paramId");
            List<String> fields = (List<String>) req.getReqData().get("fields");
            List<String> unitValueName = (List<String>) req.getReqData().get("unitValueName");
            List<String> fieldstitle = (List<String>) req.getReqData().get("fieldstitle");

            StringBuffer fldList = new StringBuffer();
            for (int i = 0; i < fields.size(); i++) {
                LinkedHashMap<String, String> e = new LinkedHashMap<String, String>();
                e.put("field", fields.get(i));
                e.put("title", fieldstitle.get(i));
                e.put("unitValueName", unitValueName.get(i));
                fldList.append(fields.get(i)).append(",");
                resList.add(e);
            }

            dateRes.put("fields", resList);
            dateRes.put("formula", formula);
            Object obj = JSON.toJSON(dateRes);
            String json = obj.toString();
            String uuid;
            if (StrUtil.isEmpty(paramId)) {
                uuid = UuidUtils.generateUuid();
            } else {
                uuid = paramId;
            }
            sb.delete(0, sb.length());
            sb.append("select count(1) from custom_params where formula = ? and deleted = 0 and param_id <> ?");
            int i = this.queryForInt(conn, sb.toString(), new Object[]{formula, uuid});
            if (i > 0) {
                res.setRetMessage("该公式已存在");
                return res;
            }

            if (StrUtil.isEmpty(paramId)) {
                sb.delete(0, sb.length());
                sb.append("INSERT INTO custom_params (name,  formula, fldList, deleted, createUid, modifyUid, web_json, param_id) VALUES (?,?,?,?,?,?,?,?) ");
            } else {
                sb.delete(0, sb.length());
                sb.append("update custom_params set name=?, formula=?, fldList=?, deleted=?, createUid=?, modifyUid=?, web_json=? where param_id=?");
            }
            this.update(conn, sb.toString(), new Object[]{name, formula, fldList.toString(),0, userId, userId, json, uuid});


            if (StrUtil.isNotEmpty(paramId)) {    // 编辑 先删除
                sb.delete(0, sb.length());
                sb.append("UPDATE custom_params_bind_device SET deleted = 1 where param_id =?");
                this.update(conn, sb.toString(), new Object[]{uuid});
            }

            sb.delete(0, sb.length());
            sb.append("INSERT INTO custom_params_bind_device (param_id, deleted, createUid, modifyUid, sb_id) VALUES (?,?,?,?,?)");
            List<Object[]> list = new ArrayList<>();
            for (String sbid : sbids) {
                list.add(new Object[]{uuid, 0, userId, userId, sbid});
            }
            this.updateBatch(conn, sb.toString(), list);

            res.setRetCode(0);
            res.setRetMessage("ok");
            conn.commit();
        } finally {
            if (conn != null) {
                conn.close();
            }
            if (conn_ope != null) {
                conn_ope.close();
            }
        }
        return res;
    }

    public PageData getCustomParams(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询定时任务失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();

            String areaCode = (String) req.getReqData().get("areaCode");
            String deviceType = (String) req.getReqData().get("deviceType");
            String deviceModel = (String) req.getReqData().get("deviceModel");
            String protocolId = (String) req.getReqData().get("protocolId");
            sb.append("select a.param_id, a.name, date_format(a.createTime, '%Y-%m-%d %T') as createTime, p.protocol_id, p.name as protocolName, GROUP_CONCAT(distinct b.sb_id) as sbids, " +
                    " GROUP_CONCAT(distinct substr(d.xzqh, 1, 2)) as xzqh, GROUP_CONCAT(distinct d.xzqh) as xzqhdm, GROUP_CONCAT(distinct t.xzqhmc) as xzqhmc, " +
                    " a.formula, a.fldList, a.web_json, s.sblx_id as deviceModel, s.sbxh as deviceModelName, v.dic_no as deviceType, v.title as deviceTypeName," +
                    " count( distinct b.sb_id) as deviceCount \n" +
                    " from custom_params a\n" +
                    " inner join custom_params_bind_device b on b.param_id = a.param_id and b.deleted = 0 \n" +
                    " inner join nes_ope_prod.is_equipment_info c on c.sb_id = b.sb_id and c.sfsc = 0\n" +
                    " inner join nes_ope_prod.is_install_info d on d.dd_id = c.dd_id and d.sfsc = 0\n" +
                    " inner join nes_ope_prod.t_xzqh t on t.xzqhdm like concat(substr(d.xzqh, 1, 2),'%') and t.xzqhjb = 1\n" +
                    " inner join nes_ope_prod.t_sblx s on s.sblx_id = c.sblx_id\n" +
                    " inner join nes_ope_prod.is_dict_value_info v on v.dic_no = s.sblx and type_id = 'd_type_2'\n" +
                    " inner join nes_ope_prod.is_wxsb w on w.wx_id = c.wx_id\n" +
                    " inner join nes_ope_prod.is_device_protocol_info p on p.protocol_id = w.protocol_id and p.is_deleted = 0\n" +
                    " where a.deleted = 0");
            sb.append(StrUtil.isEmpty(protocolId) ? "" : " and p.protocol_id = '" + protocolId +"'");
            sb.append(StrUtil.isEmpty(areaCode) ? "" : " and t.xzqhdm = '" + areaCode +"'");
            sb.append(StrUtil.isEmpty(deviceType) ? "" : " and v.dic_no = '" + deviceType +"'");
            sb.append(StrUtil.isEmpty(deviceModel) ? "" : " and s.sbxh = '" + deviceModel +"'");
            sb.append(" group by a.param_id, p.protocol_id order by a.createTime desc");
            StringBuffer sbfrom = new StringBuffer();
            sbfrom.append(sb.toString()) ;
            int idx=sbfrom.indexOf(" from ") ;
            sbfrom.delete(0, idx);
            Page page = new Page(conn,sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> list = this.queryForList(conn,sb.toString(), new Object[]{});

            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }

    public PageData deleteCustomParams(DataReq req) throws Exception {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除定时任务失败");
        Connection conn = null;
        StringBuffer sb = new StringBuffer();
        try {
            conn = DBUtils.getJNDIDBConn();

            List<String> paramIds = (List<String>) req.getReqData().get("ids");
            for (String paramId : paramIds) {
                // 删除预警主表
                sb.append("update custom_params set deleted = 1 where param_id=?");
                this.update(conn, sb.toString(), new Object[]{paramId});

                // 删除预警设备
                sb.delete(0, sb.length());
                sb.append("update custom_params_bind_device set deleted = 1 where param_id=?");
                this.update(conn, sb.toString(), new Object[]{paramId});
            }
            res.setRetCode(0);
            res.setRetMessage("ok");
            conn.commit();
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
        return res;
    }
}
